package com.culturalCenter.dataCenter.mapper.site;

import com.culturalCenter.dataCenter.customEntity.site.CustomSiteUsedCount;
import com.culturalCenter.dataCenter.entity.site.SiteBookingMonth;
import com.culturalCenter.dataCenter.mapper.base.BaseMapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * SiteBookingMonth的Dao
 */
@Mapper
public interface SiteBookingMonthMapper extends BaseMapper<SiteBookingMonth> {

    /**
     * 根据片区和日期查询场地近七天使用情况
     *
     * @param area
     * @param date
     *
     * @return
     */
    @Select("SELECT siteName, a.num1/b.num2 usedRate FROM (SELECT id, siteName FROM cdc_site WHERE area = '') s, " +
            "(SELECT siteId id,COUNT(*) num1 FROM cdc_site_booking_month sbm WHERE sbm.`status` !=0 AND DATE_FORMAT(beginTime, '%y%M%d') > DATE_SUB('',INTERVAL 7 DAY) GROUP BY siteId) a ," +
            "(SELECT siteId id,COUNT(*) num2 FROM cdc_site_booking_month sbm WHERE DATE_FORMAT(beginTime, '%y%M%d') > DATE_SUB(#{date},INTERVAL 7 DAY) GROUP BY siteId) b " +
            "WHERE s.id = a.id AND s.id = b.id")
    List<CustomSiteUsedCount> queryByDate(@Param("area") String area, @Param("date") Date date);

    /**
     * 根据片区和日期查询某月场地使用情况
     *
     * @param area
     * @param date
     *
     * @return
     */
    @Select("SELECT siteName, a.num1/b.num2 usedRate FROM (SELECT id, siteName FROM cdc_site WHERE area = '') s , " +
            "(SELECT siteId id,COUNT(*) num1 FROM cdc_site_booking_month sbm WHERE sbm.`status` !=0 AND DATE_FORMAT(beginTime, '%M') = DATE_FORMAT(#{date}, '%M') GROUP BY siteId) a ," +
            "(SELECT siteId id,COUNT(*) num2 FROM cdc_site_booking_month sbm WHERE DATE_FORMAT(beginTime, '%M') = DATE_FORMAT(#{date}, '%M') GROUP BY siteId) b " +
            "WHERE s.id = a.id AND s.id = b.id")
    List<CustomSiteUsedCount> queryByMonth(@Param("area") String area, @Param("date") Date date);

    /**
     * 根据日期查询近七天热门场地
     *
     * @param date
     *
     * @return
     */
    @Select("SELECT siteName, num1 bookingNum, a.num1/b.num2 usedRate FROM (SELECT id, siteName FROM cdc_site WHERE area = '') s , " +
            "(SELECT siteId id,COUNT(*) num1 FROM cdc_site_booking_month sbm WHERE sbm.`status` !=0 AND DATE_FORMAT(beginTime, '%y%M%d') > DATE_SUB('',INTERVAL 7 DAY) GROUP BY siteId ORDER BY num1 DESC) a ," +
            "(SELECT siteId id,COUNT(*) num2 FROM cdc_site_booking_month sbm WHERE DATE_FORMAT(beginTime, '%y%M%d') > DATE_SUB(NOW(),INTERVAL 7 DAY) GROUP BY siteId ORDER BY num2 DESC) b " +
            "WHERE s.id = a.id AND s.id = b.id")
    List<CustomSiteUsedCount> queryHostSiteListByDate(Date date);

    /**
     * 根据日期查询某月热门场地
     *
     * @param date
     *
     * @return
     */
    @Select("SELECT siteName, num1 bookingNum, a.num1/b.num2 usedRate FROM (SELECT id, siteName FROM cdc_site WHERE area = '') s , " +
            "(SELECT siteId id,COUNT(*) num1 FROM cdc_site_booking_month sbm WHERE sbm.`status` !=0 AND DATE_FORMAT(beginTime, '%M') = DATE_FORMAT(#{date}, '%M') GROUP BY siteId ORDER BY num1 DESC) a ," +
            "(SELECT siteId id,COUNT(*) num2 FROM cdc_site_booking_month sbm WHERE DATE_FORMAT(beginTime, '%M') = DATE_FORMAT(#{date}, '%M') GROUP BY siteId ORDER BY num2 DESC) b " +
            "WHERE s.id = a.id AND s.id = b.id")
    List<CustomSiteUsedCount> queryHostSiteListByMonth(Date date);

    /**
     * 根据日期查询近七天空闲场地(只能做到统计一天的空闲场地)
     *
     * @param date
     *
     * @return
     */
    @Select("SELECT siteId, COUNT(n = 0) num FROM " +
            "(SELECT siteId, COUNT(*) n FROM cdc_site_booking_month sbm WHERE sbm.`status` !=0 AND DATE_FORMAT(beginTime, '%y%M%d') = DATE_FORMAT(NOW(), '%y%M%d') GROUP BY siteId) c GROUP BY siteId")
    List<CustomSiteUsedCount> queryUnusedSiteListByDate(Date date);

    /**
     * 查询当前正在使用的场地数量
     *
     * @param status
     * @param date
     *
     * @return
     */
    @Select("SELECT COUNT(*) FROM cdc_site_booking_month WHERE beginTime <= #{date} AND endTime >= #{date} AND `status` = #{status}")
    int selectCountByStatus(@Param("status") Integer status, @Param("date") Date date);

    /**
     * 查场地基本信息的本月统计数据
     *
     * @param siteCode
     *
     * @return
     */
    @Select("SELECT COUNT(1) bookingNum, serveNum, serveHours FROM cdc_site_booking_month a LEFT JOIN( SELECT id, SUM( peopleNum ) serveNum, SUM( DATE_FORMAT( TIMEDIFF( endTime, beginTime ), '%H' ) ) serveHours FROM cdc_site_booking_month WHERE STATUS = 4 AND DATE_FORMAT( beginTime, '%Y-%m' ) = DATE_FORMAT(NOW(), '%Y-%m' ) AND siteCode = #{siteCode} GROUP BY siteCode ) b ON a.id = b.id WHERE STATUS != 0 AND DATE_FORMAT( beginTime, '%Y-%m' ) = DATE_FORMAT(NOW(), '%Y-%m' ) AND siteCode = #{siteCode} GROUP BY siteCode")
    CustomSiteUsedCount selectSiteInfoCount(String siteCode);

    /**
     * 根据日期查询排期数量
     *
     * @param siteCode
     * @param date
     *
     * @return
     */
    @Select("SELECT COUNT(*) bookingNum FROM cdc_site_booking_month sbm LEFT JOIN cdc_site s ON s.refId = sbm.siteId " +
            "WHERE sbm.`status` !=0 AND DATE_FORMAT(sbm.beginTime, '%y%M') = DATE_FORMAT(#{date}, '%y%M') AND s.code = #{siteCode}")
    int selectTotal(@Param("siteCode") String siteCode, @Param("date") Date date);

    /**
     * 根据人员类型（内部人员和外部人员）查询 使用场地的使用次数（本月）
     *
     * @param siteCode
     * @param userType
     *
     * @return
     */
    @Select("SELECT COUNT(*) num FROM cdc_site_booking_month sbm LEFT JOIN cdc_site s ON s.refId = sbm.siteId " +
            "WHERE sbm.`status` = 4 AND DATE_FORMAT(sbm.beginTime, '%y%M') = DATE_FORMAT(NOW(), '%y%M') AND sbm.userType = #{userType} AND s.code = #{siteCode}")
    Integer selectSiteInfoByUserType(@Param("siteCode") String siteCode, @Param("userType") int userType);

    /**
     * 根据日期区间统计本月的场地预约
     *
     * @param siteId
     * @param startDate
     * @param endDate
     *
     * @return
     */
//    @Select("SELECT COUNT(*) FROM cdc_site_booking_month sbm LEFT JOIN cdc_site s ON s.refId = sbm.siteId " +
//            "WHERE s.refId = #{siteId} AND DATE_FORMAT(sbm.beginTime, '%d') >= #{startDate} AND DATE_FORMAT(sbm.endTime, '%d') <= #{endDate} AND sbm.`status` NOT IN (-3, -1, 0)")
    Integer selectSiteInfoBookingCountByDate(@Param("siteId") String siteId, @Param("startDate") int startDate, @Param("endDate") int endDate);

    /**
     * 清空表
     */
    @Delete("TRUNCATE cdc_site_booking_month")
    void deleteAll();

    /**
     * 批量插入
     *
     * @param siteBookingMonthList
     */
    void insertBatch(List<SiteBookingMonth> siteBookingMonthList);

}
